package com.google.code.sms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import com.google.code.sms.Logger;
import com.google.code.sms.domain.MediaElement;
import com.google.code.sms.domain.MediaElement.MediaElementType;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

/**
 *
 * @author scott2ware
 */
public class MediaElementDao extends AbstractDao 
{    
    private static final Logger LOG = Logger.getLogger("MediaElementDao");
    private static final String COLUMNS = "id, element_type, name, path, folder, parent_path, parent_name, play_count, last_played, created, changed, last_scanned, children_last_updated, present, format, file_size";
    private final RowMapper rowMapper = new MediaElementDao.MediaElementMapper();
    
    /**
     * Returns the media element for the given path.
     *
     * @param path The path.
     * @return The media element or null.
     */
    public MediaElement getMediaElement(String path) 
    {        
        return queryOne("select " + COLUMNS + " from media_element where path=?", rowMapper, path);
    }

    /**
     * Returns the media element for the given ID.
     *
     * @param id The ID.
     * @return The media element or null.
     */
    public MediaElement getMediaElement(int id) {
        return queryOne("select " + COLUMNS + " from media_element where id=?", rowMapper, id);
    }
    
     /**
     * Creates or updates a media element.
     *
     * @param element The media element to create/update.
     */
    public void createOrUpdate(MediaElement element) {
                
        String sql = "update media_element set "
                + "element_type=?,"
                + "name=?,"
                + "folder=?,"
                + "parent_path=?,"
                + "parent_name=?,"
                + "play_count=?,"
                + "last_played=?,"
                + "created=?,"
                + "changed=?,"
                + "last_scanned=?,"
                + "children_last_updated=?,"
                + "present=?, "
                + "format=?,"
                + "file_size=?"
                + "where path=?";

        int n = update(sql, element.getMediaElementType(), element.getName(), element.getFolder(), element.getParentPath(), element.getParentName(), element.getPlayCount(), element.getLastPlayed(), element.getCreated(), element.getChanged(), element.getLastScanned(), element.getChildrenLastUpdated(), element.isPresent(), element.getFormat(), element.getFileSize(), element.getPath());
        
        if (n == 0) {

            update("insert into media_element (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")", null,
                    element.getMediaElementType(), element.getName(), element.getPath(), element.getFolder(), element.getParentPath(), element.getParentName(), element.getPlayCount(), element.getLastPlayed(), element.getCreated(), element.getChanged(), element.getLastScanned(), element.getChildrenLastUpdated(), element.isPresent(), element.getFormat(), element.getFileSize());
        }

        int id = queryForInt("select id from media_element where path=?", null, element.getPath());
        element.setId(id);
    }
    
    public void deleteMediaElement(String path)
    {
        update("update media_element set present=false, children_last_updated=? where path=?", new Date(0L), path);
    }
    
    public void deleteAllMediaElements()
    {
        update("delete from media_element");
    }
    
    public void markPresent(String path, Date lastScanned) 
    {
        update("update media_element set present=?, last_scanned=? where path=?", true, lastScanned, path);
    }

    public void markNotPresent(Date lastScanned) 
    {
        Date childrenLastUpdated = new Date(0L);  // Used to force a rescan if file is later resurrected.
            
        update("update media_element set present=false, children_last_updated=? where last_scanned != ? and present", childrenLastUpdated, lastScanned);
    }

    public void expunge() {
        update("delete from media_element where not present");
        update("checkpoint");
    }
    
    /**
     * Returns the most frequently played directory elements.
     *
     * @param offset Number of directory elements to skip.
     * @param count Maximum number of directory elements to return.
     * @return The most frequently played directory elements.
     */
    public List<MediaElement> getFrequentlyPlayedDirectoryElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and play_count > 0 and present "
                + "order by play_count desc limit ? offset ?"
                , rowMapper, MediaElementType.MEDIA_DIRECTORY.name(), count, offset);
    }
    
    /**
     * Returns the most frequently played audio elements.
     *
     * @param offset Number of audio elements to skip.
     * @param count Maximum number of audio elements to return.
     * @return The most frequently played audio elements.
     */
    public List<MediaElement> getFrequentlyPlayedAudioElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and play_count > 0 and present "
                + "order by play_count desc limit ? offset ?"
                , rowMapper, MediaElementType.AUDIO.name(), count, offset);
    }
    
    /**
     * Returns the most frequently played video elements.
     *
     * @param offset Number of video elements to skip.
     * @param count Maximum number of video elements to return.
     * @return The most frequently played video elements.
     */
    public List<MediaElement> getFrequentlyPlayedVideoElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and play_count > 0 and present "
                + "order by play_count desc limit ? offset ?"
                , rowMapper, MediaElementType.VIDEO.name(), count, offset);
    }
    
    /**
     * Returns the most recently played directory elements.
     *
     * @param offset Number of directory elements to skip.
     * @param count Maximum number of directory elements to return.
     * @return The most recently played directory elements.
     */
    public List<MediaElement> getRecentlyPlayedDirectoryElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and last_played is not null and present "
                + "order by last_played desc limit ? offset ?"
                , rowMapper, MediaElementType.MEDIA_DIRECTORY.name(), count, offset);
    }
    
    /**
     * Returns the most recently played audio elements.
     *
     * @param offset Number of audio elements to skip.
     * @param count Maximum number of audio elements to return.
     * @return The most recently played audio elements.
     */
    public List<MediaElement> getRecentlyPlayedAudioElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and last_played is not null and present "
                + "order by last_played desc limit ? offset ?"
                , rowMapper, MediaElementType.AUDIO.name(), count, offset);
    }
    
    /**
     * Returns the most recently played video elements.
     *
     * @param offset Number of video elements to skip.
     * @param count Maximum number of video elements to return.
     * @return The most recently played video elements.
     */
    public List<MediaElement> getRecentlyPlayedVideoElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and last_played is not null and present "
                + "order by last_played desc limit ? offset ?"
                , rowMapper, MediaElementType.VIDEO.name(), count, offset);
    }
    
     /**
     * Returns the most recently added media elements.
     *
     * @param offset Number of media elements to skip.
     * @param count Maximum number of media elements to return.
     * @return The most recently added media elements.
     */
    public List<MediaElement> getRecentlyAddedDirectoryElements(int offset, int count) {
        return query("select " + COLUMNS + " from media_element where element_type=? and present order by created desc limit ? offset ?"
                ,rowMapper, MediaElementType.MEDIA_DIRECTORY.name(), count, offset);
    }
    
    /**
     * Returns directories in alphabetical order.
     *
     * @param offset Number of directories to skip.
     * @param count Maximum number of directories to return.
     * @return Parent directories in alphabetical order.
     */
    public List<MediaElement> getAlphabeticalDirectoryElements(int offset, int count)
    {  
        return query("select " + COLUMNS + " from media_element where element_type=? and present order by name desc limit ? offset ?", 
                rowMapper, MediaElementType.MEDIA_DIRECTORY.name(), count, offset);
    }
    
    /**
     * Returns media elements that are direct children of the given path.
     *
     * @param path The path.
     * @return The list of children.
     */
    public List<MediaElement> getChildrenOf(String path) {
        return query("select " + COLUMNS + " from media_element where parent_path=? and present", rowMapper, path);
    }
    
    public List<MediaElement> getElementsInPlaylist(int playlistId) {
        return query("select " + prefix(COLUMNS, "media_element") + " from media_element, playlist_element where "
                + "media_element.id = playlist_element.media_element_id and "
                + "playlist_element.playlist_id = ? and "
                + "media_element.present order by playlist_element.id", rowMapper, playlistId);
    }
    
    private static class MediaElementMapper implements ParameterizedRowMapper<MediaElement> 
    {
        @Override
        public MediaElement mapRow(ResultSet rs, int rowNum) throws SQLException {
            
            MediaElement element = new MediaElement();
                    
            element.setId(rs.getInt(1));
            element.setMediaElementType(MediaElementType.valueOf(rs.getString(2)));
            element.setName(rs.getString(3));
            element.setPath(rs.getString(4));
            element.setFolder(rs.getString(5));
            element.setParentPath(rs.getString(6));
            element.setParentName(rs.getString(7));
            element.setPlayCount(rs.getInt(8));
            element.setLastPlayed(rs.getTimestamp(9));
            element.setCreated(rs.getTimestamp(10));
            element.setChanged(rs.getTimestamp(11));
            element.setLastScanned(rs.getTimestamp(12));
            element.setChildrenLastUpdated(rs.getTimestamp(13));
            element.setPresent(rs.getBoolean(14));
            element.setFormat(rs.getString(15));
            element.setFileSize(rs.getLong(16));
            
            return element;
        }
    }
}
